﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common; 
using System.Text;
using Npgsql;
using NpgsqlTypes;

namespace MYear.ODA.Adapter
{
    public class DbAPostgreSQL : DBAccess
    {
        public DbAPostgreSQL(string ConnectionString)
            : base(ConnectionString)
        {
        }
        public override string[] ObjectFlag
        {
            get { return new string[] { "\"", "\"" }; }
        }
        private NpgsqlConnection _DBConn = null;
        protected override IDbConnection GetConnection()
        {
            if (_DBConn == null)
            {
                _DBConn = new NpgsqlConnection(ConnString);
                //_DBConn.StateChange += _DBConn_StateChange;  
            }
            if (_DBConn.State == ConnectionState.Closed)
                _DBConn.Open();
            return _DBConn;
        }

        //private void _DBConn_StateChange(object sender, StateChangeEventArgs e)
        //{
        //    if (e.CurrentState == ConnectionState.)
        //    {

        //    }
        //}

      
        protected override DbDataAdapter GetDataAdapter(IDbCommand SelectCmd)
        {
            return new NpgsqlDataAdapter((NpgsqlCommand)SelectCmd);
        }
        public override DateTime GetDBDateTime()
        {
            IDbCommand Cmd = OpenCommand();
            try
            {
                string sql = " select   localtimestamp as  DB_DATETIME ";
                Cmd.CommandText = sql;
                Cmd.CommandType = CommandType.Text;
                return Convert.ToDateTime(Cmd.ExecuteScalar());
            }
            finally
            {
                CloseCommand(Cmd);
            }
        }
        public override DbAType DBAType { get { return DbAType.PostgreSQL; } }




        public override string[] GetUserTables()
        {
            DataTable dt_table = Select("SELECT tablename as TABLE_NAME from pg_tables where schemaname = 'public'", null);
            string[] str = new string[dt_table.Rows.Count];
            for (int i = 0; i < str.Length; i++)
            {
                str[i] = dt_table.Rows[i]["TABLE_NAME"].ToString();
            }
            return str;
        }
        public override string[] GetUserViews()
        {
            DataTable dt_table = Select("SELECT  VIEWNAME as VIEW_NAME FROM   pg_views  WHERE schemaname ='public'", null);
            string[] str = new string[dt_table.Rows.Count];
            for (int i = 0; i < str.Length; i++)
            {
                str[i] = dt_table.Rows[i]["VIEW_NAME"].ToString();
            }
            return str;
        }

 
        public override DataTable GetTableColumns()
        {
            StringBuilder sql_tabcol = new StringBuilder().Append("select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION AS COL_SEQ, DATA_TYPE AS DATATYPE,")
                .Append("   CASE IS_NULLABLE WHEN 'YES' THEN 'N' ELSE 'Y' END AS NOT_NULL,")
                .Append(" CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END AS LENGTH, ")
                .Append(" null as DIRECTION,")
                .Append(" NUMERIC_SCALE AS SCALE")
                .Append(" from information_schema.columns c,pg_tables t") 
                .Append(" where c.TABLE_NAME = t.tablename ")
                .Append(" AND  table_schema = 'public'")
                .Append(" and table_catalog = '" + this.Database + "'")
                .Append(" ORDER BY TABLE_NAME,ORDINAL_POSITION"); 
            DataTable Dt = Select(sql_tabcol.ToString(), null);
            Dt.TableName = "TABLE_COLUMN";
            return Dt;
        }
        public override DataTable GetViewColumns()
        {

            StringBuilder sql_tabcol = new StringBuilder().Append("select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION AS COL_SEQ, DATA_TYPE AS DATATYPE,")
                .Append("   CASE IS_NULLABLE WHEN 'YES' THEN 'N' ELSE 'Y' END AS NOT_NULL,")
                .Append(" CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END AS LENGTH, ")
                .Append(" null as DIRECTION,")
                .Append(" NUMERIC_SCALE AS SCALE")
                .Append(" from information_schema.columns c,pg_views t")
                .Append(" where c.TABLE_NAME = t.viewname ")
                .Append(" AND  table_schema = 'public'")
                .Append(" and table_catalog = '" + this.Database + "'")
                .Append(" ORDER BY TABLE_NAME,ORDINAL_POSITION");
            DataTable Dt = Select(sql_tabcol.ToString(), null);
            Dt.TableName = "VIEW_COLUMN";
            return Dt;
        }

        public override string[] GetPrimarykey(string TableName)
        {
            string PrimaryCols = new StringBuilder().Append("select conname as COLUMN_NAME from pg_constraint c,pg_class t")
                .Append("where c.contype = 'p'")
                .Append("and c.conrelid = t.oid")
                .Append("and t.relname = '" + TableName + "'").ToString();

            DataTable Dt = this.Select(PrimaryCols, null);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                List<string> cols = new List<string>();
                for (int i = 0; i < Dt.Rows.Count; i++)
                    cols.Add(Dt.Rows[i]["COLUMN_NAME"].ToString());
                return cols.ToArray();
            }
            return new string[0];
        }


        public override Dictionary<string, string[]> GetPrimarykey()
        {
            string[] tables = GetUserTables();
            Dictionary<string, string[]> pkeys = new Dictionary<string, string[]>();
            foreach (var t in tables)
            {
                string[] pKeys = GetPrimarykey(t);
                pkeys.Add(t, pKeys);
            }
            return pkeys;
        }

        public override string[] GetUserProcedure()
        {
            DataTable dt_table = Select("SELECT name as PROCEDURE_NAME FROM sys.objects o WHERE   o.type = 'P'", null);
            string[] str = new string[dt_table.Rows.Count];
            for (int i = 0; i < str.Length; i++)
            {
                str[i] = dt_table.Rows[i]["PROCEDURE_NAME"].ToString();
            }
            return str;
        }
        public override DataTable Select(string SQL, ODAParameter[] ParamList, int StartIndex, int MaxRecord, string Orderby)
        {
            string BlockStr = SQL + " limit " + MaxRecord.ToString()  + " offset " + StartIndex.ToString(); ///取出MaxRecord条记录
            return Select(BlockStr, ParamList);
        }
        public override List<T> Select<T>(string SQL, ODAParameter[] ParamList, int StartIndex, int MaxRecord, string Orderby)
        {
            IDbCommand Cmd = OpenCommand();
            IDataReader Dr = null;
            try
            {
                string BlockStr = SQL + " limit " + MaxRecord.ToString() + " offset " + StartIndex.ToString() ;
                Cmd.CommandType = CommandType.Text;
                SetCmdParameters(ref Cmd, BlockStr, ParamList);
                Dr = Cmd.ExecuteReader();
                var rlt = GetList<T>(Dr);
                return rlt;
            }
            finally
            {
                if (Dr != null)
                {
                    try
                    {
                        Cmd.Cancel();
                    }
                    catch { }
                    Dr.Close();
                    Dr.Dispose();
                }
                CloseCommand(Cmd);
            }
        }

        public override bool Import(DataTable Data, ODAParameter[] Prms)
        {
            NpgsqlConnection conn = null;
            DataTable ImportData = Data.Copy();
            try
            {
                List<string> lsColNames = new List<string>();
                for (int i = 0; i < Prms.Length; i++)
                {
                    if (!ImportData.Columns.Contains(Prms[i].ColumnName))
                        throw new ODAException(701, string.Format("Import data into table [{0}] error: ColumnName [{1}]  is no matched", ImportData.TableName, Prms[i].ColumnName));

                    lsColNames.Add($"{Prms[i].ColumnName}");
                }
               
                string copyString = $"COPY {ImportData.TableName } ( {string.Join(",", lsColNames) } ) FROM STDIN (FORMAT BINARY)";

                conn = (NpgsqlConnection)this.GetConnection();
                if (conn.State == ConnectionState.Closed)
                    conn.Open();  
                var writer = conn.BeginBinaryImport(copyString); 

                foreach (DataRow row in ImportData.Rows)
                {
                    writer.StartRow(); 
                    for (int c = 0; c < Prms.Length; c++)
                    {
                        NpgsqlDbType tp = NpgsqlDbType.Varchar; 
                        switch (Prms[c].DBDataType)
                        {
                            case ODAdbType.ODatetime:
                                tp = NpgsqlDbType.Date;
                                break;
                            case ODAdbType.ODecimal:
                                tp = NpgsqlDbType.Numeric;
                                break;
                            case ODAdbType.OBinary:
                                tp = NpgsqlDbType.Oid;
                                break;
                            case ODAdbType.OInt:
                                tp = NpgsqlDbType.Integer;
                                break;
                            case ODAdbType.OChar:
                                tp = NpgsqlDbType.Char;
                                break;
                            case ODAdbType.OVarchar:
                            default:
                                tp = NpgsqlDbType.Varchar;
                                break; 
                        } 
                        writer.Write(row[Prms[c].ColumnName], tp);
                    }
                }
                writer.Complete();
                conn.Close();  
                return true;

            }
            catch (Exception ex)
            {
                throw new ODAException(202, string.Format("Import data into table [{0}] error:{1}", ImportData.TableName, ex.Message));
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        } 
        public override object GetExpressResult(string ExpressionString)
        {
            IDbCommand Cmd = OpenCommand();
            try
            {
                string sql = " SELECT" + ExpressionString + " AS VALUE ";
                Cmd.CommandText = sql;
                Cmd.CommandType = CommandType.Text;
                return Cmd.ExecuteScalar();
            }
            finally
            {
                CloseCommand(Cmd);
            }
        }

        protected override void SetCmdParameters(ref IDbCommand Cmd, string SQL, params ODAParameter[] ParamList)
        {
            if (ParamList != null)
            {
                foreach (ODAParameter pr in ParamList)
                {
                    NpgsqlParameter param = new NpgsqlParameter();
                    param.ParameterName = pr.ParamsName;
                    if (pr.Size < 0)
                        param.Size = 1;
                    else
                        param.Size = pr.Size;
                    param.Direction = pr.Direction;
                    switch (pr.DBDataType)
                    {
                        case ODAdbType.ODatetime:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
                            if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
                            {
                                param.Value = System.DBNull.Value;
                            }
                            else
                            {
                                if (pr.ParamsValue.ToString().Trim() == "")
                                {
                                    param.Value = System.DBNull.Value;
                                }
                                else
                                {
                                    if (pr.ParamsValue is DateTime || pr.ParamsValue is DateTime?)
                                    {
                                        param.Value = pr.ParamsValue;
                                    }
                                    else if (string.IsNullOrWhiteSpace(pr.ParamsValue.ToString().Trim()))
                                    {
                                        param.Value = System.DBNull.Value;
                                    }
                                    else
                                    {
                                        param.Value = Convert.ToDateTime(pr.ParamsValue);
                                    }
                                }
                            }
                            break;
                        case ODAdbType.ODecimal:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Numeric;
                            if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
                            {
                                param.Value = System.DBNull.Value;
                            }
                            else
                            {
                                if (pr.ParamsValue is decimal || pr.ParamsValue is decimal?)
                                {
                                    param.Value = pr.ParamsValue;
                                }
                                else if (string.IsNullOrWhiteSpace(pr.ParamsValue.ToString().Trim()))
                                {
                                    param.Value = System.DBNull.Value;
                                }
                                else
                                {
                                    param.Value = Convert.ToDecimal(pr.ParamsValue);
                                }
                            }
                            break;
                        case ODAdbType.OBinary:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Oid;
                            if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
                            {
                                param.Value = System.DBNull.Value;
                            }
                            else
                            {
                                param.Value = pr.ParamsValue;
                                if (pr.ParamsValue is byte[])
                                {
                                    param.Size = ((byte[])pr.ParamsValue).Length;
                                }
                                else
                                {
                                    throw new ODAException(201, "Params :" + pr.ParamsName + " Type must be byte[]");
                                }
                            }
                            break;
                        case ODAdbType.OInt:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                            if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
                            {
                                param.Value = System.DBNull.Value;
                            }
                            else
                            {
                                if (pr.ParamsValue is int || pr.ParamsValue is int?)
                                {
                                    param.Value = pr.ParamsValue;
                                }
                                else if (string.IsNullOrWhiteSpace(pr.ParamsValue.ToString().Trim()))
                                {
                                    param.Value = System.DBNull.Value;
                                }
                                else
                                {
                                    param.Value = Convert.ToInt32(pr.ParamsValue);
                                }
                            }
                            break;
                        case ODAdbType.OChar:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Char;
                            if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
                            {
                                param.Value = System.DBNull.Value;
                            }
                            else
                            {
                                if (pr.ParamsValue.ToString().Trim() == "")
                                {
                                    param.Value = System.DBNull.Value;
                                }
                                else
                                {
                                    param.Value = pr.ParamsValue.ToString().Trim();
                                }
                            }
                            break;
                        case ODAdbType.OVarchar:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                            if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
                            {
                                param.Value = System.DBNull.Value;
                            }
                            else
                            {
                                if (pr.ParamsValue.ToString().Trim() == "")
                                {
                                    param.Value = System.DBNull.Value;
                                }
                                else
                                {
                                    param.Value = pr.ParamsValue.ToString().Trim();
                                }
                            }
                            break;
                        default:
                            param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                            param.Value = pr.ParamsValue;
                            break;
                    }
                    ((NpgsqlParameterCollection)Cmd.Parameters).Add(param);
                }
            }
            Cmd.CommandText = SQL;
            FireExecutingCommand(Cmd);
        }
    }
} 